<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

/**
 * Drink
 * 
 * This model manages drink related informations.
 * Operates the following tables:
 * - drink
 * - table 2
 * 
 * @package     Main
 * @author      Lukas Fritsch <jejanim@gmail.com>
 * @version     0.1 (07.08.2013)
 */    

class Drinkmodel extends CI_Model{
    private $tbl_drink          = 'drink';
    private $tbl_category       = 'category';
    private $tbl_cat_drink      = 'drink_category';
    private $tbl_ing_drink      = 'drink_ingredient';
    private $tbl_ingredient     = 'ingredient';
    private $tbl_type           = 'type';
    private $tbl_measurand      = 'measurand';
    private $tbl_rating         = 'rating';
    
    
    public function __construct(){
        parent::__construct();
    }  
    
 
    public function get_all_drinks(){
        $sql = 'SELECT id,
                       name
                FROM drink';
        $query = $this->db->query($sql);
        
        if($query->num_rows == 0)
            return FALSE;
        
//        foreach($query->result() as $drink){
//            $sql = 'SELECT c.id,
//                           c.name
//                    FROM ? as c
//                        INNER JOIN ? as dc
//                            ON c.id = dc.category_id
//                    WHERE dc.drink_id = ?';
//            $subquery = $this->db->query($sql, array(
//                                                     $this->tbl_category,
//                                                     $this->tbl_cat_drink,
//                                                     $drink->id
//                                                    ));
//            if($subquery->num_rows == 0)
//                continue;
//            
//            $drink->categories = $subquery->result();
//        }
        
        return $query->result();
    }
    
    /**
     * GET USER DRINKS
     * Fetches a limited amount of recipes created by the given user. 
     * If no limit is given, five records are shown.
     * 
     * @param type $user_id - the user's id
     * @param type $limit   - record limit, e.g. top 5
     * @return boolean      - an array of objects or an empty array
     */
    public function get_user_drinks($user_id = FALSE, $limit = 5){
        if(!$user_id || !$limit)
            return FALSE;
        
        $sql = 'SELECT id, name, updated, created, image_path
                FROM drink
                WHERE user_id=?
                ORDER BY updated, created DESC
                LIMIT ?';
        $query = $this->db->query($sql, array($user_id, $limit));
        
        if($query->num_rows() == 0)
            return FALSE;
        
        return $query->result();
    }
    
    /**
     * GET DETAILS
     * Retrieves any relevant information about a certain drink
     * 
     * @param int $drink_id - The id of the drink that is in question.
     */
    public function get_details($drink_id = FALSE){
        if(!$drink_id)
            return FALSE;
        
        $sql = 'SELECT d.id, 
                       d.name, 
                       d.created,
                       d.updated,
                       d.instruction,
                       d.image_path,
                       u.id,
                       u.username              
                FROM drink as d
                INNER JOIN user as u
                ON d.user_id = u.id
                WHERE d.id=?';
        $query = $this->db->query($sql, $drink_id);
        
        if($query->num_rows() == 1) 
            return $query->row();
            
        return FALSE;
        
        
    }
    
    /**
     * GET CATEGORIES
     * Fetches all categories a drink belongs to.
     * 
     * @param type $drink_id
     */
    public function get_categories($drink_id = FALSE){
        if(!$drink_id)
            return FALSE;
        
        $sql = 'SELECT c.id,
                       c.name
                FROM ? as c
                INNER JOIN ? as dc
                    ON c.id = dc.category_id
                WHERE dc.drink_id = ?';
        $query = $this->db->query($sql, array(
                                              $this->category_tbl, 
                                              $this->pivot, 
                                              $drink_id
                                             ));
        if($query->num_rows == 0)
            return FALSE;
        
        return $query->result();
    }
    
    /**
     * GET INGREDIENTS
     * Returns all ingredients a drink recipe has listed.
     * 
     * @param type $drink_id
     */
    public function get_ingredients($drink_id = FALSE){
        if(!$drink_id)
            return FALSE;
        
        $sql = 'SELECT i.name,
                       t.name as type,
                       m.name as measurand,
                       di.amount as amount
                FROM ? as i
                    INNER JOIN ? as di
                            ON i.id = di.ingredient_id
                    INNER JOIN ? as m
                            ON i.measurand_id = m.id
                    INNER JOIN ? as t
                            ON i.type_id = t.id
                WHERE di.drink_id = ?';
        
        $query = $this->db->query($sql, array(
                                              $this->tbl_ingredient,
                                              $this->tbl_ing_drink,
                                              $this->tbl_measurand,
                                              $this->tbl_type,
                                              $drink_id
                                              ));
        //check if we have a result to return
        if($query->num_rows == 0)
            return FALSE;
        
        return $query->result();
    }
    
    /**
     * GET RATING
     * Fetches the average rating a drink received by any user that
     * did a rating. Rating is calculated by the SQL AVG function.
     * 
     * 
     * @param type $drink_id
     * @return boolean
     */
    public function get_rating($drink_id = FALSE){
        if(!$drink_id)
            return FALSE;
        
        $sql = 'SELECT AVG(rating) as avg,
                       COUNT(*) as count
                FROM ?
                WHERE drink_id = ?';
        $query = $this->db->query($sql, array(
                                              $this->tbl_rating,
                                              $drink_id
                                             ));
        
        //if no record or more than one is returned, break
        if($query->num_rows == 0 || $query->num_rows > 1)
            return FALSE;
        
        return $query->row();
    }
    
}